package com.xinghou.platform.dao.main.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.xinghou.platform.dao.bases.JdbcTemplate;
import com.xinghou.platform.dao.bases.database.CustomerContextHolder;
import com.xinghou.platform.dao.main.StoreDAO;
import com.xinghou.platform.model.main.Store;
import com.xinghou.platform.server.mq.MqMessage;
/**
 * @author liuwei.wen
 *
 */
@Service
public class StoreDAOImpl  {/*
	@Resource
	private JdbcTemplate jdbcTemplate;
	

	@Resource
	private MqMessage mqMessage;

	
	@Override
	public Store getStoreBystoreId(Integer storeId) throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="select * from store where Store_id = ?";
		Store store = jdbcTemplate.queryForObject(sql, new Object[] {storeId}, new StoresMapper());
		CustomerContextHolder.clearCustomerType();
		return store;

	}

	@Override
	public int updateStore(Store store) throws DataAccessException {

		StringBuffer strSQL = new StringBuffer(
				"UPDATE store SET NAME =? ,Address =?,LEVEL =?, Average_consume =?,Business_hours=?,Branch_store=?,Telephone=?,Area_id=?,Type_id=?,Longitude=?,Latitude=?,TYPE=?,Sale_count=?,Buy_count=?,Browse_count=?,City_id=?,another_name=?,status=? Where Store_id =?"	
				);
		Object[] params = {store.getName(), store.getAddress(), store.getLevel(), store.getAverageConsume(),store.getBusinessHours(),
				store.getBranchStore(),store.getTelephone(),store.getAreaId(),store.getTypeId(),store.getLongitude(),store.getLatitude(),store.getType(),store.getSaleCount(),store.getBuyCount(),store.getBrowseCount(),store.getCityId(),store.getAnotherName(),store.getStatus(),store.getStoreId()
		};
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_WRITE);
		int ret = jdbcTemplate.update(strSQL.toString(),params);
		CustomerContextHolder.clearCustomerType();
		if(ret>0){
			mqMessage.sendMessageStore(store.getStoreId()+"",true);
		}
		return ret;
	}

	@Override
	public int saveStore(Store store) throws DataAccessException {
		StringBuffer strSQL = new StringBuffer(
				"INSERT INTO store (NAME,Address,LEVEL,Average_consume,Business_hours,Branch_store,Telephone,Area_id,Type_id,Longitude,Latitude,TYPE,Sale_count,Buy_count,Browse_count,City_id,another_name,User_id,status,createtime)"
				+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
				);
		Object[] params = {store.getName(), store.getAddress(), store.getLevel(), store.getAverageConsume(),store.getBusinessHours(),
				store.getBranchStore(),store.getTelephone(),store.getAreaId(),store.getTypeId(),store.getLongitude(),store.getLatitude(),store.getType(),
				store.getSaleCount(),store.getBuyCount(),store.getBrowseCount(),store.getCityId(),store.getAnotherName(),store.getUserid(),store.getStatus(),store.getCreatetime()
		};
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_WRITE);
		int ret =jdbcTemplate.save(strSQL.toString(),params);
		CustomerContextHolder.clearCustomerType();
		if(ret>0){
			mqMessage.sendMessageStore(ret+"",false);
		}
		return ret;
	}

	@Override
	public List<String> getStoreInfoList(String key, String frompage) {
		int num=Integer.valueOf(frompage);
		String keys="%"+key+"%";
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="SELECT NAME FROM store  where NAME like ? LIMIT ?,10";
		List<String> storename = jdbcTemplate.query(sql, new Object[] {keys,num}, new StringMapper());
		CustomerContextHolder.clearCustomerType();
		return storename;
	}
	
	@Override
	public List<String> getStoreNames(String key, String frompage) {
		int num=Integer.valueOf(frompage);
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="SELECT NAME FROM store  where NAME=? LIMIT ?,10";
		List<String> storename = jdbcTemplate.query(sql, new Object[] {key,num}, new StringMapper());
		CustomerContextHolder.clearCustomerType();
		return storename;
	}
	
	//模糊查询名字
	@Override
	public List<String> getStoreInfoLists(String key, String frompage) {
		int num=Integer.valueOf(frompage);
		String keys="%"+key+"%";
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="SELECT DISTINCT SUBSTRING_INDEX(NAME,'(',1)as NAME FROM store  where TYPE = 'SYSTEM' AND SUBSTRING_INDEX(NAME,'(',1) like ? LIMIT ?,20";
		List<String> store = jdbcTemplate.query(sql, new Object[] {keys,num}, new StringMapper());
		CustomerContextHolder.clearCustomerType();
		return store;
	}
	
     //精确
//	@Override
//	public List<Store> getShopInfoDetailLists(String key, String frompage) {
//		int num=Integer.valueOf(frompage);
//		String keys="%"+key+"%";
//		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
//		String sql="SELECT A.Store_id as Store_id ,A.Name as name,A.Address as address,A.Type as type,C.name as typename,CONCAT(SUBSTR(B.uri,8),B.File_name)AS shopurl FROM store A "
//				+ "LEFT JOIN store_image B ON  A.Store_id=B.Store_id   "
//				+ "LEFT JOIN store_type C ON A.Type_id=C.Type_id  where SUBSTRING_INDEX(A.NAME,'(',1) like ? and B.Image_type_id=999 LIMIT ?,10";
//		List<Store>store = jdbcTemplate.query(sql, new Object[] {keys,num}, new StoresMapper());
//		if(store.size()<=0){
//			sql="SELECT A.Store_id as Store_id ,A.Name as name,A.Address as address,A.Type as type,C.name as typename,CONCAT(SUBSTR(B.uri,8),B.File_name)AS shopurl FROM store A "
//					+ "LEFT JOIN store_image B ON  A.Store_id=B.Store_id   "
//					+ "LEFT JOIN store_type C ON A.Type_id=C.Type_id  where SUBSTRING_INDEX(A.NAME,'(',1) like ? LIMIT ?,10";
//			store = jdbcTemplate.query(sql, new Object[] {keys,num}, new StoresMapper());
//		}
//		CustomerContextHolder.clearCustomerType();
//		return store;
//	}
	
	//统计结果
	@Override
	public List<Store> getStoreInfoListnum(String key) {
		String keys="%"+key+"%";
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="select * from store where TYPE = 'SYSTEM' AND NAME like ?";
		List<Store>storelist = jdbcTemplate.query(sql, new Object[] {keys}, new StoresMapper());
		CustomerContextHolder.clearCustomerType();
		return storelist;
	}
	
	@Override
	public List<Store> getStoreList() throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="select store_id,city_id from store";
		List<Store> StoreList=jdbcTemplate.query(sql,new StoresMapper1());
		return StoreList;
	}
	
	public List<Store> getStoreListbyname(String key, String frompage,String shopid,String cityid)throws DataAccessException {
		String keys= key+"%";
		int num=Integer.valueOf(frompage);
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="select * from store where TYPE = 'SYSTEM' AND NAME like ? AND Store_id != ? AND City_id = ? LIMIT ?,10";
		List<Store> storelist = jdbcTemplate.query(sql, new Object[] {keys,shopid,cityid,num}, new StoresMapper());
		CustomerContextHolder.clearCustomerType();
		return storelist;
	}
	
	@Override
	public List<Store> getStoreListbyname(String key, String frompage)throws DataAccessException {
		String keys="%"+key+"%";
		int num=Integer.valueOf(frompage);
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="select * from store where TYPE = 'SYSTEM' AND NAME like ? or Address like ? LIMIT ?,10";
		List<Store> storelist = jdbcTemplate.query(sql, new Object[] {keys,keys,num}, new StoresMapper());
		CustomerContextHolder.clearCustomerType();
		return storelist;
	}
	
	public List<Store> getStoreListbynameforsharecard(String key)throws DataAccessException {
		String keys="%"+key+"%";
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="select * from store where TYPE = 'SYSTEM' AND NAME like ? LIMIT 0,10";
		List<Store> storelist = jdbcTemplate.query(sql, new Object[] {keys}, new StoresMapper());
		CustomerContextHolder.clearCustomerType();
		return storelist;
	}
	
	public List<Store> getStoreList(int storeid, int page)throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="select * from store WHERE Store_id > ? limit ?";
		List<Store> storelist = jdbcTemplate.query(sql, new Object[] {storeid,page}, new StoresMapper());
		CustomerContextHolder.clearCustomerType();
		return storelist;
	}
	
	public List<Integer> getStoreInfoLists(List<Integer> storeids)throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT Store_id FROM store WHERE Store_id IN (");
		
		for (int i = 0; i < storeids.size(); i++) {
			sql.append(storeids.get(i));
			if(i!=(storeids.size()-1))
				sql.append(",");
		}
			
		sql.append(") ORDER BY Sale_count DESC");
		
		return jdbcTemplate.query(sql.toString(), new IntegerMapper());
	}
	
	public Integer getBranchStoreCount(String key,String shopid,String cityid)throws DataAccessException {
		String keys= key+"%";
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="select count(*) from store where TYPE = 'SYSTEM' AND City_id = ? AND NAME like ? AND Store_id != ?  ";
		int returnnum = jdbcTemplate.queryForObject(sql, new Object[] {cityid,keys,shopid}, new IntegerMapper());
		CustomerContextHolder.clearCustomerType();
		return returnnum;
	}
	
	private static final class StoresMapper implements RowMapper<Store> {
		*//*public Store mapRow(ResultSet rs, int rowNum) throws SQLException {
			Store store = new Store();
			store.setStoreId(rs.getInt("Store_id"));
			store.setName(rs.getString("Name"));
			store.setAddress(rs.getString("Address"));
			//store.setLevel(rs.getShort("LEVEL"));
			//store.setAverageConsume(rs.getShort("Average_consume"));
			store.setBusinessHours(rs.getString("Business_hours"));
			store.setBranchStore(rs.getString("Branch_store"));
			store.setTelephone(rs.getString("Telephone"));
			store.setAreaId(rs.getInt("Area_id"));
			store.setTypeId(rs.getString("Type_id"));
			store.setLongitude(rs.getDouble("Longitude"));
			store.setLatitude(rs.getDouble("Latitude"));
			store.setType(rs.getString("TYPE"));
			store.setSaleCount(rs.getInt("Sale_count"));
			store.setBuyCount(rs.getInt("Buy_count"));
			store.setBrowseCount(rs.getInt("Browse_count"));
			store.setCityId(rs.getInt("City_id"));
			store.setAnotherName(rs.getString("another_name"));
			store.setStatus(rs.getInt("status"));
			store.setUserid(rs.getString("User_id"));
			//store.setCreatetime(rs.getTimestamp("createtime"));*//*
			return null;
		}
	}
	
	private static final class StringMapper implements RowMapper<String> {
		public String mapRow(ResultSet rs, int rowNum) throws SQLException {
			return rs.getString(1);
		}
	}
	
	private static final class IntegerMapper implements RowMapper<Integer> {
		public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
			return rs.getInt(1);
		}
	}

	public Store getStoreBystoreName(String storeName)
			throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="select * from store where name = ?";
		Store store = jdbcTemplate.queryForObject(sql, new Object[] {storeName}, new StoresMapper());
		CustomerContextHolder.clearCustomerType();
		return store;
	}

	public List<Store> getStoreListByName(String name,String cityid,String address)
			throws DataAccessException {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="select * "
				+ "from store WHERE name=? and City_id=? and Address=?";
		List<Store> StoreList=jdbcTemplate.query(sql,new Object[] {name,cityid,address},new StoresMapper());
		return StoreList;
	}

	@Override
	public List<Store> getByUserId(int userId, int limit) {
		CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_READ);
		String sql="SELECT * FROM store WHERE TYPE='USER' AND user_id=?  ORDER BY Store_id  DESC  LIMIT ? ";
		List<Store> list = jdbcTemplate.query(sql, new Object[] {userId,limit}, new StoresMapper());
		CustomerContextHolder.clearCustomerType();
		return list;
	}
	

	private static final class StoresMapper1 implements RowMapper<Store> {
		public Store mapRow(ResultSet rs, int rowNum) throws SQLException {
			Store store = new Store();
			store.setStoreId(rs.getInt("Store_id"));
			store.setCityId(rs.getInt("City_id"));
			return store;
		}
	}*/
}
